﻿

using System;
using System.Data;
using Model;
namespace Dal
{
	public class DbNewOrdersDal
	{
		public DbNewOrdersDal()
		{
		}
 		#region Insert
 		public static int Insert(DbNewOrders dbNewOrders)
		{	
			string sql = @"Insert Into DbNewOrders (
					OrderSN,
					MemberID,
					SessionID,
					Discount,
					Email,
					TotalPrice,
					ShippingPrice,
					PayAdditionalFee,
					ShippingTitle,
					ShippingFirstName,
					ShippingLastName,
					ShippingAddressLine1,
					ShippingAddressLine2,
					ShippingCity,
					ShippingState,
					ShippingCountry,
					ShippingPostalCode,
					ShippingPhone,
					BillingTitle,
					BillingFirstName,
					BillingLastName,
					BillingAddressLine1,
					BillingAddressLine2,
					BillingCity,
					BillingState,
					BillingPostalCode,
					BillingCountry,
					BillingPhone,
					Express,
					TrackingNumber,
					ShippingTime,
					FirstPrice,
					FirstWeight,
					ExtWeight,
					ExtPrice,
					TotalWeight,
					Comments,
					PaymentMethod,
					PaymentOnlineCurrency,
					OrderTime,
					OrderStatus,
					CancelReason,
					CutStock,
					PaymentReturnSN,
					PaymentReturnMsg
				) values('{0}',{1},'{2}',{3},'{4}',{5},{6},{7},'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}',{31},{32},{33},{34},{35},'{36}','{37}','{38}','{39}',{40},'{41}',{42},'{43}','{44}')";
			sql = string.Format(sql,
					SqlHelper.String(dbNewOrders.OrderSN),
					dbNewOrders.MemberID,
					SqlHelper.String(dbNewOrders.SessionID),
					dbNewOrders.Discount,
					SqlHelper.String(dbNewOrders.Email),
					dbNewOrders.TotalPrice,
					dbNewOrders.ShippingPrice,
					dbNewOrders.PayAdditionalFee,
					SqlHelper.String(dbNewOrders.ShippingTitle),
					SqlHelper.String(dbNewOrders.ShippingFirstName),
					SqlHelper.String(dbNewOrders.ShippingLastName),
					SqlHelper.String(dbNewOrders.ShippingAddressLine1),
					SqlHelper.String(dbNewOrders.ShippingAddressLine2),
					SqlHelper.String(dbNewOrders.ShippingCity),
					SqlHelper.String(dbNewOrders.ShippingState),
					SqlHelper.String(dbNewOrders.ShippingCountry),
					SqlHelper.String(dbNewOrders.ShippingPostalCode),
					SqlHelper.String(dbNewOrders.ShippingPhone),
					SqlHelper.String(dbNewOrders.BillingTitle),
					SqlHelper.String(dbNewOrders.BillingFirstName),
					SqlHelper.String(dbNewOrders.BillingLastName),
					SqlHelper.String(dbNewOrders.BillingAddressLine1),
					SqlHelper.String(dbNewOrders.BillingAddressLine2),
					SqlHelper.String(dbNewOrders.BillingCity),
					SqlHelper.String(dbNewOrders.BillingState),
					SqlHelper.String(dbNewOrders.BillingPostalCode),
					SqlHelper.String(dbNewOrders.BillingCountry),
					SqlHelper.String(dbNewOrders.BillingPhone),
					SqlHelper.String(dbNewOrders.Express),
					SqlHelper.String(dbNewOrders.TrackingNumber),
					dbNewOrders.ShippingTime,
					dbNewOrders.FirstPrice,
					dbNewOrders.FirstWeight,
					dbNewOrders.ExtWeight,
					dbNewOrders.ExtPrice,
					dbNewOrders.TotalWeight,
					dbNewOrders.Comments,
					SqlHelper.String(dbNewOrders.PaymentMethod),
					SqlHelper.String(dbNewOrders.PaymentOnlineCurrency),
					dbNewOrders.OrderTime,
					dbNewOrders.OrderStatus,
					dbNewOrders.CancelReason,
					dbNewOrders.CutStock,
					SqlHelper.String(dbNewOrders.PaymentReturnSN),
					SqlHelper.String(dbNewOrders.PaymentReturnMsg)
				);
			return SqlHelper.ExecSql(sql);
		}
		#endregion
		
		#region Update
 		public static int Update(DbNewOrders dbNewOrders)
		{	
			string sql = @"Update DbNewOrders set 

					OrderSN= '{0}',
					MemberID= {1},
					SessionID= '{2}',
					Discount= {3},
					Email= '{4}',
					TotalPrice= {5},
					ShippingPrice= {6},
					PayAdditionalFee= {7},
					ShippingTitle= '{8}',
					ShippingFirstName= '{9}',
					ShippingLastName= '{10}',
					ShippingAddressLine1= '{11}',
					ShippingAddressLine2= '{12}',
					ShippingCity= '{13}',
					ShippingState= '{14}',
					ShippingCountry= '{15}',
					ShippingPostalCode= '{16}',
					ShippingPhone= '{17}',
					BillingTitle= '{18}',
					BillingFirstName= '{19}',
					BillingLastName= '{20}',
					BillingAddressLine1= '{21}',
					BillingAddressLine2= '{22}',
					BillingCity= '{23}',
					BillingState= '{24}',
					BillingPostalCode= '{25}',
					BillingCountry= '{26}',
					BillingPhone= '{27}',
					Express= '{28}',
					TrackingNumber= '{29}',
					ShippingTime= '{30}',
					FirstPrice= {31},
					FirstWeight= {32},
					ExtWeight= {33},
					ExtPrice= {34},
					TotalWeight= {35},
					Comments= '{36}',
					PaymentMethod= '{37}',
					PaymentOnlineCurrency= '{38}',
					OrderTime= '{39}',
					OrderStatus= {40},
					CancelReason= '{41}',
					CutStock= {42},
					PaymentReturnSN= '{43}',
					PaymentReturnMsg= '{44}'
				where

					OrderId= {45}";
			sql = string.Format(sql,
					SqlHelper.String(dbNewOrders.OrderSN),
					dbNewOrders.MemberID,
					SqlHelper.String(dbNewOrders.SessionID),
					dbNewOrders.Discount,
					SqlHelper.String(dbNewOrders.Email),
					dbNewOrders.TotalPrice,
					dbNewOrders.ShippingPrice,
					dbNewOrders.PayAdditionalFee,
					SqlHelper.String(dbNewOrders.ShippingTitle),
					SqlHelper.String(dbNewOrders.ShippingFirstName),
					SqlHelper.String(dbNewOrders.ShippingLastName),
					SqlHelper.String(dbNewOrders.ShippingAddressLine1),
					SqlHelper.String(dbNewOrders.ShippingAddressLine2),
					SqlHelper.String(dbNewOrders.ShippingCity),
					SqlHelper.String(dbNewOrders.ShippingState),
					SqlHelper.String(dbNewOrders.ShippingCountry),
					SqlHelper.String(dbNewOrders.ShippingPostalCode),
					SqlHelper.String(dbNewOrders.ShippingPhone),
					SqlHelper.String(dbNewOrders.BillingTitle),
					SqlHelper.String(dbNewOrders.BillingFirstName),
					SqlHelper.String(dbNewOrders.BillingLastName),
					SqlHelper.String(dbNewOrders.BillingAddressLine1),
					SqlHelper.String(dbNewOrders.BillingAddressLine2),
					SqlHelper.String(dbNewOrders.BillingCity),
					SqlHelper.String(dbNewOrders.BillingState),
					SqlHelper.String(dbNewOrders.BillingPostalCode),
					SqlHelper.String(dbNewOrders.BillingCountry),
					SqlHelper.String(dbNewOrders.BillingPhone),
					SqlHelper.String(dbNewOrders.Express),
					SqlHelper.String(dbNewOrders.TrackingNumber),
					dbNewOrders.ShippingTime,
					dbNewOrders.FirstPrice,
					dbNewOrders.FirstWeight,
					dbNewOrders.ExtWeight,
					dbNewOrders.ExtPrice,
					dbNewOrders.TotalWeight,
					dbNewOrders.Comments,
					SqlHelper.String(dbNewOrders.PaymentMethod),
					SqlHelper.String(dbNewOrders.PaymentOnlineCurrency),
					dbNewOrders.OrderTime,
					dbNewOrders.OrderStatus,
					dbNewOrders.CancelReason,
					dbNewOrders.CutStock,
					SqlHelper.String(dbNewOrders.PaymentReturnSN),
					SqlHelper.String(dbNewOrders.PaymentReturnMsg),
					dbNewOrders.OrderId);
			return SqlHelper.ExecSql(sql);
		}
		#endregion
 
 

 		#region Delete
 		public static int Delete(Int32 orderId)
		{
		
			string sql = @"Delete from DbNewOrders where 
					OrderId= {0}";
			sql = string.Format(sql,
					orderId
);
			return SqlHelper.ExecSql(sql);
		} 
		#endregion
 
 		#region Select
 		
 		public static DataSet Select()
		{
			string sql = "select a.* from DbNewOrders a "; 
			return SqlHelper.Select(sql);
		}
				
		public static DbNewOrders Select(Int32 orderId)
		{
			DbNewOrders dbNewOrders = null;
			string sql = @"select a.* from DbNewOrders a  where
					a.OrderId= {0}
";
			sql = string.Format(sql,
					orderId
);
			DataSet ds = SqlHelper.Select(sql);
			if(SqlHelper.HasRow(ds))
				dbNewOrders = new DbNewOrders(ds.Tables[0].Rows[0]);					
			return dbNewOrders;
		}
		
		public static DataSet Select(string where)
		{ 
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;
			string sql = "select a.* from DbNewOrders a  where 1=1 " + where ;
			return SqlHelper.Select(sql);
		}
		
		public static DataSet SelectForList(string where,int pageIndex,int pageSize,bool bCount,out int count )
		{
			count = 0;
						
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;
			string sql = "select a.* from dbNewOrders a where 1=1 " + where;
                
			return SqlHelper.Select(sql,pageSize,pageIndex,bCount,ref count);
		}		
		public static DataSet Select(string where,int pageIndex,int pageSize,bool bCount,out int count )
		{
			count = 0;			
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;			
			string sql = "select a.* from DbNewOrders a  where 1=1 " + where;
			return SqlHelper.Select(sql,pageSize,pageIndex,bCount,ref count);
		}
		#endregion
	}
}
